148

Beginner’s Guide to Code Algorithms

148

Step 3

Invoke the form you created in step 1.

Step 4

Initialize variables and file paths, get the data from the website, load the US

maps, and populate the data sheet in the format of the “Any State” sheet men­

tioned above.

Step 5

Sort the state-​based summary in descending order.

Step 6

Draw the first chart (all states’ cumulative COVID count).

Step 7

Draw the first state in the second chart.

Step 8

Add the other states to the second chart.

Here is the code:

STEP 2

Set up the sheets that you need to do the data manipulation.

Sub CovidGraphPrepare()

  Dim NewBook As Workbook

  Dim filepath As String

  Dim startrow(500) As Integer

  Dim rRange As Range

  Dim fileopenWb As Workbook, thisWb As Workbook

  Dim fileopenMapWb As Workbook

  Dim fileopenWs As Worksheet, thisWs As Worksheet

  StatesString =​ “AK,AL,AR,AZ,CA,CO,CT,DC,DE,FL,GA,HI,IA,ID,IL,IN,KS,KY,L

A,MA,MD,ME,MI,MN,MO, MS,MT,NC,ND,NE,NH,NJ,NM,NV,NY,OH,OK,OR,PA,R

I,SC,SD,TN,TX,UT,VA,VT,WA,WI,WV,WY”

  States =​ Split(StatesString, “,”)

  ‘ Get a new Workbook

  If FirstTime =​ “Then

    Set NewBook =​ Workbooks.Add

    Do

      fName =​ Application.GetSaveAsFilename

      Loop Until fName <> False

      NewBook.SaveAs Filename:=​fName

      CurrentWorkbookName =​ ActiveWorkbook.Name

      FirstTime =​ “N”

    End If

   

    ‘ Create all sheets required ***

    FoundSheet =​ 0

    For Each Sheet In ActiveWorkbook.Worksheets